import pandas as pd
from pandasrw import dump
import os
import random
import numpy as np
import sqlite3
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule
from openpyxl.utils import get_column_letter
import flet as fl
from datetime import datetime
import timeit
def format_data(file_path):
    wb=load_workbook(file_path,keep_links=False)
    ws=wb.active
    red_fill=PatternFill(start_color='FF0000',end_color='FF0000',fill_type='solid')
    blue_fill=PatternFill(start_color='004C99',end_color='004C99',fill_type='solid')
    max_col=ws.max_column+1
    max_row=ws.max_row+1
    for col in range(4,max_col):
        max=ws.cell(2,col).value
        min=ws.cell(3,col).value
        g_rule=CellIsRule(operator="greaterThan",formula=[str(max)],fill=red_fill)
        l_rule=CellIsRule(operator="lessThan",formula=[str(min)],fill=blue_fill)
        col_letter=get_column_letter(col)
        f_str=col_letter+str(7)+":"+col_letter+str(ws.max_row+100)
        ws.conditional_formatting.add(f_str,g_rule)
        ws.conditional_formatting.add(f_str,l_rule)
        for row in range(7,max_row):
            ws.cell(row,1).number_format = 'yyyy-mm-dd'
            ws.cell(row,2).number_format = 'hh:mm'
    ws.column_dimensions['A'].width = 12
    wb.save(file_path)
def main(page:fl.Page):
    page.title="添加数据"
    page.window.height=360
    page.window.width=300
    page.horizontal_alignment=fl.MainAxisAlignment.CENTER
    page.vertical_alignment=fl.CrossAxisAlignment.CENTER
    SQL_L="INSERT INTO data (worktime,filename,workstep,infomation) VALUES (?,?,?,?)"
    put_num=[]
    for i in range(2,14):
        put_num.append(i)
    for i in range(15,27):
        put_num.append(i)
    def do_file1(e):
        timea=timeit.default_timer()
        btn1.disabled=True
        btn2.disabled=True
        btn3.disabled=True
        btn1.text="程序1正在执行..."
        page.update()
        conn=sqlite3.connect('data.db')
        cr=conn.cursor()
        cr.execute('''CREATE TABLE IF NOT EXISTS data
            (id INTEGER PRIMARY KEY, worktime TEXT,filename TEXT,workstep TEXT,infomation TEXT)''')
        def do_sql(name,step,remark):
            Time=datetime.now().strftime("%Y-%m-%d_%H:%M:%S_%f")
            cr.execute(SQL_L,(Time,name,step,remark))
            conn.commit()
        do_sql("add_data","开始程序","开始运行程序")
        base=load_workbook("d:/add_data.xlsx",read_only=True,data_only=True)
        base_sheet=base.active
        f_b=[]
        for i in range(1,base_sheet.max_row+1):
            local=[]
            for j in range(1,28):
                local.append(base_sheet.cell(i,j).value)
            if_do=False
            if local[1] is not None:
                for i_n in range(2,14):
                    if local[i_n] is not None:
                        if_do=True
            if local[1] is not None:
                for i_n in range(15,27):
                    if local[i_n] is not None:
                        if_do=True
            local.append(if_do)
            f_b.append(local)
        base.close()
        do_sql("add_data","读取完成","收录需要增加的信息")
        FILE=[]
        folder_path='D:/data'
        for root, dirs, files in os.walk(folder_path):
            for filename in files:
                if '.xlsx' in filename:
                    file_path = os.path.join(root, filename)
                    FILE.append([file_path,filename])
        for file in FILE:
            filename=file[1]
            file_path=file[0]
            for fb in f_b:
                if filename== fb[0] and fb[27]==True:
                    do_sql(str(filename),"开始加载","pandas打开文件")
                    df = pd.read_excel(file_path)
                    maxrow=len(df)
                    do_sql(str(filename),"读取规格信息","开始读取")
                    last_row = df.iloc[maxrow - 1]
                    last_row_list = last_row.tolist()
                    add_num=last_row_list[2]#重要，需要增加的行数
                    column_name=df.columns.to_list()#获取列名
                    #准备好32组数据的标准差和平均值
                    spec=[]
                    for col in range(3,len(column_name)):
                        coldata=df[column_name[col]].tolist()
                        max=coldata[0]
                        min=coldata[1]
                        data=coldata[-32:]
                        mean = sum(data) / len(data)
                        variance = sum((x - mean) ** 2 for x in data) / len(data)
                        std_dev = variance ** 0.5
                        if std_dev<=0.008:
                            std_dev=0.01
                        spec.append([std_dev,mean,max,min])
                    length=len(spec)
                    for x in put_num:
                        if fb[x] is not None:
                            for ii in range(add_num):
                                if(x<15):
                                    new_row=[fb[1],fb[x],ii+1]
                                else:
                                    new_row=[fb[14],fb[x],ii+1]
                                for jj in range(0,length):
                                    rand_value = round(np.random.normal(spec[jj][1], spec[jj][0]),3)
                                    if rand_value>spec[jj][2]:
                                        rand_value=round(spec[jj][2]-random.uniform(0.002,0.008),3)
                                    elif rand_value<spec[jj][3]:
                                        rand_value=round(spec[jj][3]+random.uniform(0.002,0.008),3)
                                    new_row.append(rand_value)
                                df.loc[maxrow+ii] = new_row
                            maxrow+=add_num
                            btn1.text="成功增加一笔数据\n"+filename
                            page.update()
                            if(x<15):
                                do_sql(str(filename),"增加数据","新增日期_"+str(fb[1])+"新增时间_"+str(fb[x]))
                            else:
                                do_sql(str(filename),"增加数据","新增日期_"+str(fb[14])+"新增时间_"+str(fb[x]))
                    df.to_excel(file_path, index=False)
                    do_sql(str(filename),"保存文件","清空格式")
                    del df
            do_sql(str(filename),"增加格式","openpyxl重新打开")
            format_data(file_path)
            btn1.text="文件格式已调整完成\n"+filename
            page.update()
            do_sql(str(filename),"增加格式","关闭并保存文件")
        timeb=timeit.default_timer()
        gap=timeb-timea
        btn1.text="程序1已执行完毕\n用时"+str(gap)[:10]+"秒"
        btn1.disabled=False
        btn2.disabled=False
        btn3.disabled=False
        page.update()
        do_sql("全部文件","程序完成","P1:"+str(gap)[:10])
    def do_file2(e):
        timea=timeit.default_timer()
        btn1.disabled=True
        btn2.disabled=True
        btn3.disabled=True
        btn2.text="程序2正在执行..."
        page.update()
        conn=sqlite3.connect('data.db')
        cr=conn.cursor()
        cr.execute('''CREATE TABLE IF NOT EXISTS data
            (id INTEGER PRIMARY KEY, worktime TEXT,filename TEXT,workstep TEXT,infomation TEXT)''')
        def do_sql(name,step,remark):
            Time=datetime.now().strftime("%Y-%m-%d_%H:%M:%S_%f")
            cr.execute(SQL_L,(Time,name,step,remark))
            conn.commit()
        do_sql("add_data","开始程序","开始运行程序")
        base=load_workbook("d:/add_data.xlsx",read_only=True,data_only=True)
        base_sheet=base.active
        f_b=[]
        for i in range(1,base_sheet.max_row+1):
            local=[]
            for j in range(1,28):
                local.append(base_sheet.cell(i,j).value)
            if_do=False
            if local[1] is not None:
                for i_n in range(2,14):
                    if local[i_n] is not None:
                        if_do=True
            if local[1] is not None:
                for i_n in range(15,27):
                    if local[i_n] is not None:
                        if_do=True
            local.append(if_do)
            f_b.append(local)
        base.close()
        do_sql("add_data","读取完成","收录需要增加的信息")
        FILE=[]
        folder_path='D:/data'
        for root, dirs, files in os.walk(folder_path):
            for filename in files:
                if '.xlsx' in filename:
                    file_path = os.path.join(root, filename)
                    FILE.append([file_path,filename])
        for file in FILE:
            filename=file[1]
            file_path=file[0]
            for fb in f_b:
                if filename== fb[0] and fb[27]==True:
                    do_sql(str(filename),"开始加载","pandas打开文件")
                    df = pd.read_excel(file_path)
                    maxrow=len(df)
                    do_sql(str(filename),"读取规格信息","开始读取")
                    last_row = df.iloc[maxrow - 1]
                    last_row_list = last_row.tolist()
                    add_num=last_row_list[2]#重要，需要增加的行数
                    column_name=df.columns.to_list()#获取列名
                    #准备好32组数据的标准差和平均值
                    spec=[]
                    for col in range(3,len(column_name)):
                        coldata=df[column_name[col]].tolist()
                        max=coldata[0]
                        min=coldata[1]
                        data=coldata[-32:]
                        mean = sum(data) / len(data)
                        variance = sum((x - mean) ** 2 for x in data) / len(data)
                        std_dev = variance ** 0.5
                        if std_dev<=0.008:
                            std_dev=0.01
                        spec.append([std_dev,mean,max,min])
                    length=len(spec)
                    for x in put_num:
                        if fb[x] is not None:
                            for ii in range(add_num):
                                if(x<15):
                                    new_row=[fb[1],fb[x],ii+1]
                                else:
                                    new_row=[fb[14],fb[x],ii+1]
                                for jj in range(0,length):
                                    rand_value = round(np.random.normal(spec[jj][1], spec[jj][0]),3)
                                    if rand_value>spec[jj][2]:
                                        rand_value=round(spec[jj][2]-random.uniform(0.002,0.008),3)
                                    elif rand_value<spec[jj][3]:
                                        rand_value=round(spec[jj][3]+random.uniform(0.002,0.008),3)
                                    new_row.append(rand_value)
                                df.loc[maxrow+ii] = new_row
                            maxrow+=add_num
                            btn2.text="成功增加一笔数据\n"+filename
                            page.update()
                            if(x<15):
                                do_sql(str(filename),"增加数据","新增日期_"+str(fb[1])+"新增时间_"+str(fb[x]))
                            else:
                                do_sql(str(filename),"增加数据","新增日期_"+str(fb[14])+"新增时间_"+str(fb[x]))
                    df.to_excel(file_path, index=False)
                    do_sql(str(filename),"保存文件","清空格式")
                    del df
        timeb=timeit.default_timer()
        gap=timeb-timea
        btn2.text="程序2已执行完毕\n用时"+str(gap)[:10]+"秒"
        btn1.disabled=False
        btn2.disabled=False
        btn3.disabled=False
        page.update()
        do_sql("全部文件","程序完成","P2:"+str(gap)[:10])
    def do_file3(e):
        timea=timeit.default_timer()
        btn1.disabled=True
        btn2.disabled=True
        btn3.disabled=True
        btn3.text="程序3正在执行..."
        page.update()
        conn=sqlite3.connect('data.db')
        cr=conn.cursor()
        cr.execute('''CREATE TABLE IF NOT EXISTS data
            (id INTEGER PRIMARY KEY, worktime TEXT,filename TEXT,workstep TEXT,infomation TEXT)''')
        def do_sql(name,step,remark):
            Time=datetime.now().strftime("%Y-%m-%d_%H:%M:%S_%f")
            cr.execute(SQL_L,(Time,name,step,remark))
            conn.commit()
        do_sql("add_data","开始程序","开始运行程序")
        FILE=[]
        folder_path='D:/data'
        for root, dirs, files in os.walk(folder_path):
            for filename in files:
                if '.xlsx' in filename:
                    file_path = os.path.join(root, filename)
                    FILE.append([file_path,filename])
        for file in FILE:
            filename=file[1]
            file_path=file[0]
            format_data(file_path)
            btn3.text="文件格式已调整完成\n"+filename
            page.update()
        do_sql(str(filename),"增加格式","关闭并保存文件")
        timeb=timeit.default_timer()
        gap=timeb-timea
        btn3.text="程序3已执行完毕\n用时"+str(gap)[:10]+"秒"
        btn1.disabled=False
        btn2.disabled=False
        btn3.disabled=False
        page.update()
        do_sql("全部文件","程序完成","P3:"+str(gap)[:10])
    btn1=fl.ElevatedButton(text="执行全套程序",width=page.window.width,height=page.window.height*0.25,color=fl.colors.BLUE,bgcolor=fl.colors.YELLOW_100,on_click=do_file1)
    btn2=fl.ElevatedButton(text="只增加数据",width=page.window.width,height=page.window.height*0.25,color=fl.colors.BLUE,bgcolor=fl.colors.YELLOW_100,on_click=do_file2)
    btn3=fl.ElevatedButton(text="只调整格式",width=page.window.width,height=page.window.height*0.25,color=fl.colors.BLUE,bgcolor=fl.colors.YELLOW_100,on_click=do_file3)
    page.add(btn1,btn2,btn3)
fl.app(target=main)